package srdb.migration.dbhandler.srdb;

import oracle.jdbc.OracleResultSet;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import org.srdbsql.util.SRInterval;
import org.srdbsql.util.SRmoney;
import srdb.migration.MigrationToolkit;
import srdb.migration.Utility;
import srdb.migration.common.IMTKConnection;
import srdb.migration.common.MTKBridge;
import srdb.migration.common.MTKConstants;
import srdb.migration.common.MTKConstants.DATABASE_TYPE;
import srdb.migration.dbhandler.enterprisedb.EnterpriseDBConnection;
import srdb.migration.dbhandler.enterprisedb.RedwoodBridge;
import srdb.migration.i18n.ResourceHandler;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.Table;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SRDBBridge extends MTKBridge {
    protected static final String BLOB_FILE_NAME = "mtk_blobdata";
    protected static final String CLOB_FILE_NAME = "mtk_clobdata";
    protected static String fileCharEncoding = null;

    protected RedwoodLobHandler redwoodLobHandler = null;

    public SRDBBridge(IMTKConnection srcConn, IMTKConnection targetConn) {
        super(srcConn, targetConn);
        this.redwoodLobHandler = new RedwoodLobHandler();
    }

    List excludeRowid = new ArrayList();

    public long setTableData(Table table, int batchSize,
                             MTKConstants.DATABASE_TYPE sourceDBType,
                             MTKConstants.DATABASE_TYPE targetDBType) throws Exception {
        this.lastMigratedDataSize = 0L;
        long committedRowCount = 0L;
        boolean lastBatchFailure = false;
        PreparedStatement insertStmt = null;
        Statement sourceStmt = null;
        Statement destStmt = null;
        ResultSet rs = null;
        String insertStr = "";
        File f = null;
        FileOutputStream fos = null;
        FileInputStream fis = null;

        FileInputStream[] arrFis = new FileInputStream[table
                .getLargeObjectsColumnCount() * batchSize];
        BufferedReader[] arrBr = new BufferedReader[table.getCLOBColumnCount()
                * batchSize];

        ByteArrayInputStream[] arrBis = new ByteArrayInputStream[table
                .getLargeObjectsColumnCount() * batchSize];
        int fisIndex = 0;
        int bisIndex = 0;
        int brIndex = 0;
        for (int i = 0; i < arrFis.length; i++) {
            arrFis[i] = null;
            arrBis[i] = null;
        }

        for (int i = 0; i < arrBr.length; i++) {
            arrBr[i] = null;
        }

        int j = 0;
        Connection _edbConn = null;
        boolean defaultAutoCommitMode = false;

        String fileNamePrefix = Utility.getValidFileNamePrefixFor(table
                .getParentSchema().getName(), table.getName());
        try {
            this.targetConn.getConnection().setAutoCommit(false);
            insertStr = "INSERT INTO " + table.getTargetSchemaQualifiedName()
                    + " VALUES (";
            for (int i = 0; i < table.getColumns().size(); i++) {
                insertStr = insertStr + "?";
                if (i < table.getColumns().size() - 1)
                    insertStr = insertStr + ", ";
            }
            insertStr = insertStr + ");";
            insertStmt = this.targetConn.getConnection().prepareStatement(
                    insertStr);

            destStmt = this.targetConn.getConnection().createStatement();

            if ((table.isPartitioned())
                    && (!MigrationToolkit.isMemberOfPartitionTableList(table)))
                destStmt.execute("DELETE FROM "
                        + table.getTargetSchemaFullyQualifiedName(
                        MTKConstants.DATABASE_TYPE.ORACLE,
                        MTKConstants.DATABASE_TYPE.ENTERPRISEDB, false));
            else {
                // schema 名称的大小写敏感.........
                destStmt.execute("TRUNCATE TABLE "
                        + table.getTargetSchemaFullyQualifiedName(
                        MTKConstants.DATABASE_TYPE.ORACLE,
                        MTKConstants.DATABASE_TYPE.ENTERPRISEDB, false));
            }

            destStmt.execute("update sr_catalog.sr_class set relhasindex = 'f' where oid = '"
                    + table.getTargetSchemaFullyQualifiedName(
                    MTKConstants.DATABASE_TYPE.ORACLE,
                    MTKConstants.DATABASE_TYPE.ENTERPRISEDB, true)
                    + "'::sr_catalog.regclass");

            this.targetConn.commit();

            sourceStmt = this.srcConn.getConnection().createStatement();

            if (((this.srcConn instanceof EnterpriseDBConnection))
                    || ((this.srcConn instanceof SRDBConnection))) {
                _edbConn = this.srcConn.getConnection();
                defaultAutoCommitMode = _edbConn.getAutoCommit();
                _edbConn.setAutoCommit(false);

                sourceStmt.setFetchSize(100);
            }

            /**
             * 获取rowid 映射
             *
             */
//            ResultSet rst = sourceStmt.executeQuery(table.getRowIdSelectSql());
//            HashMap rowidMap = new HashMap();
//            int rowidCont = 0;
//            while (rst.next()) {
//                rowidCont++;
//                rowidMap.put(rowidCont, rst.getString(1));
//
//            }


            rs = sourceStmt.executeQuery(table.getSelectSQL());
            int tmpcount = 0;
            while (rs.next()) {
                tmpcount++;
                for (int i = 0; i < table.getColumns().size(); i++) {
                    Column column = table.getColumns().get(i);
                    switch (Column.DataType.values()[table.getColumns().get(i)
                            .getDataType().ordinal()]) {
                        //
                        case DATE:
                            insertStmt.setDate(i + 1, rs.getDate(i + 1));
                            break;
                        case VARCHAR:
                        case NVARCHAR:
                        case TEXT:
                            // case BITSTRING:
                        case XML:
                            if (((column.isClob()) || (column.isNClob()))
                                    && sourceDBType == DATABASE_TYPE.ORACLE) {
                                this.redwoodLobHandler.processClob(rs, i,
                                        fileNamePrefix, brIndex, arrBr, insertStmt);
                            } else if (MigrationToolkit.replaceNullChar()) {
                                String colValue = rs.getString(i + 1);
                                if (colValue != null) {
                                    colValue = colValue.replaceAll("", String
                                            .valueOf(MigrationToolkit
                                                    .nullReplacementChar()));
                                }

                                insertStmt.setString(i + 1, colValue);
                            } else {
                                insertStmt.setString(i + 1, rs.getString(i + 1));
                            }

                            break;
                        case BINARY_FLOAT:
                            if (rs.getObject(i + 1) != null)
                                insertStmt.setDouble(i + 1, rs.getDouble(i + 1));
                            else {
                                insertStmt.setNull(i + 1, 6);
                            }

                            break;
                        case NUMERIC://
                        case FLOAT:
                        case INTEGER:
                            Object objVal = rs.getObject(i + 1);

                            if (column.isReal()) {
                                if (objVal != null)
                                    insertStmt.setFloat(i + 1, rs.getFloat(i + 1));
                                else
                                    insertStmt.setNull(i + 1, 6);
                            } else if (column.isDouble()) {
                                if (objVal != null)
                                    insertStmt.setDouble(i + 1, rs.getFloat(i + 1));
                                else {
                                    insertStmt.setNull(i + 1, 8);
                                }
                            } else if (objVal != null)
                                insertStmt.setBigDecimal(i + 1,
                                        rs.getBigDecimal(i + 1));
                            else {
                                insertStmt.setNull(i + 1, 2);
                            }

                            break;
                        case TIME:
                        case TIMESTAMP:
                            insertStmt.setTimestamp(i + 1, rs.getTimestamp(i + 1));
                            break;
                        case BYTEA:// blob等大字段处理

                            if (column.isBlob()
                                    && sourceDBType == DATABASE_TYPE.ORACLE) {
                                fisIndex = this.redwoodLobHandler.processBlob(rs,
                                        i, fileNamePrefix, fisIndex, arrFis,
                                        insertStmt);
                            } else if (column.isLong()) {
                                try {
//                                    byte[] b= rs.getObject(i + 1).toString().getBytes("UTF-8");
                                    byte[] b = rs.getBytes(i + 1);//long  存pdf、bmp时，类型Io异常 类型长度大于最大值
                                    if (b != null) {
                                        this.lastMigratedDataSize += b.length;

                                        arrBis[bisIndex] = new ByteArrayInputStream(b);
                                        insertStmt.setBinaryStream(i + 1,
                                                arrBis[(bisIndex++)], b.length);
                                    } else {
                                        insertStmt.setNull(i + 1, -2);
                                    }
                                } catch (Exception ex) {
//                                    System.out.println(rowidMap.get(tmpcount));
//                                    excludeRowid.add(rowidMap.get(tmpcount));
//                                    System.out.println(excludeRowid.toString());
//
//                                    if (insertStmt != null) {
//                                        insertStmt.close();
//                                    }
//
//                                    if (destStmt != null) {
//                                        destStmt.close();
//                                    }
//
//                                    if (rs != null) {
//                                        try {
//                                            rs.close();
//                                        } catch (Exception ex2) {
////                                            ex.printStackTrace();
//                                        }
//                                    }
//
//                                    if (sourceStmt != null) {
//                                        try {
//                                            sourceStmt.close();
//                                        } catch (Exception ex3) {
////                                            ex.printStackTrace();
//                                        }
//                                    }

//                                    System.out.println(this.srcConn.toString());
//                                    table.setWhereClause(" ROWID not in (" + excludeRowid.toString().substring(1, excludeRowid.toString().length() - 1) + ")");
//                                    this.setTableData(table, batchSize, sourceDBType, targetDBType);

                                }

                            } else {
                                byte[] b = rs.getBytes(i + 1);//long  存pdf、bmp时，类型Io异常 类型长度大于最大值
                                if (b != null) {
                                    this.lastMigratedDataSize += b.length;

                                    arrBis[bisIndex] = new ByteArrayInputStream(b);
                                    insertStmt.setBinaryStream(i + 1,
                                            arrBis[(bisIndex++)], b.length);
                                } else {
                                    insertStmt.setNull(i + 1, -2);
                                }
                            }
                            break;
                        case INTERVAL:
                            SRInterval pgInterval = null;
                            String intervalValue = rs.getString(i + 1);

                            if (intervalValue != null) {
                                pgInterval = getPGIntervalObject(intervalValue);

                                this.lastMigratedDataSize += pgInterval.toString()
                                        .getBytes().length;
                            }

                            insertStmt.setObject(i + 1, pgInterval);
                            break;
                        case BOOLEAN:
                            insertStmt.setBoolean(i + 1, rs.getBoolean(i + 1));
                            break;
                        case MONEY:
                            insertStmt.setObject(i + 1,
                                    new SRmoney(rs.getString(i + 1)));
                            break;
                        case USERDEFINED:
                        case NAME:
                        case OID:
                        case GUID:
                        case ROWID:
                        case NETWORKADDRESS:
                            insertStmt.setObject(i + 1, rs.getString(i + 1), 1111);
                            break;
                        case ARRAY:
                            insertStmt.setArray(i + 1, rs.getArray(i + 1));
                            break;
                        case GEOMETRIC:
                            insertStmt.setObject(i + 1, rs.getObject(i + 1));
                            break;
                        // case INTEGER:// add 2014-06-19
                        // insertStmt.setInt(i + 1, rs.getInt(i + 1));
                        // break;
                        default:
                            throw new Exception("Invalid DataType");
                    }
                }
                insertStmt.addBatch();
                j++;
                if (j % batchSize != 0)
                    continue;
                try {
                    insertStmt.executeBatch();
                    lastBatchFailure = false;
                    Utility.getLogger().info(
                            "["
                                    + table.getName()
                                    + "] "
                                    + ResourceHandler
                                    .getValue("INFO_MIGRATED_ROWS",
                                            new Object[]{Integer
                                                    .valueOf(j)}));
                } catch (Exception ex) {
                    Utility.getLogger().warn(
                            ex.getMessage() + ", Skipping Batch");
                    Utility.getLogger().error("Stack Trace:", ex);
                    MigrationToolkit.incrementFailureCount();
                    table.incrementBatchFailureCount();
                    lastBatchFailure = true;
                }
                insertStmt.clearBatch();
                this.targetConn.commit();

                if (!lastBatchFailure) {
                    committedRowCount += batchSize;
                }

                for (int i = 0; i < arrFis.length; i++) {
                    if (arrFis[i] != null) {
                        arrFis[i].close();
                        new File(Utility.getTempPath() + "mtk_blobdata" + "_"
                                + fileNamePrefix + i).delete();
                        arrFis[i] = null;
                    }

                    if (arrBis[i] != null) {
                        arrBis[i].close();
                        arrBis[i] = null;
                    }

                }

                for (int i = 0; i < arrBr.length; i++) {
                    if (arrBr[i] != null) {
                        arrBr[i].close();
                        new File(Utility.getTempPath() + "mtk_clobdata" + "_"
                                + fileNamePrefix + i).delete();
                        arrBr[i] = null;
                    }
                }

                fisIndex = 0;
                bisIndex = 0;
                brIndex = 0;
            }

            int[] lastBatchCmdCount = null;
            try {
                lastBatchCmdCount = insertStmt.executeBatch();
                lastBatchFailure = false;

                if (lastBatchCmdCount.length > 0) {
                    committedRowCount += j % batchSize;
                    Utility.getLogger()
                            .info("["
                                    + table.getName()
                                    + "] "
                                    + ResourceHandler
                                    .getValue(
                                            "INFO_MIGRATED_ROWS",
                                            new Object[]{Long
                                                    .valueOf(committedRowCount)}));
                }
            } catch (Exception ex) {
                Utility.getLogger().warn(ex.getMessage() + ", Skipping Batch");
                Utility.getLogger().error("Stack Trace:", ex);
                MigrationToolkit.incrementFailureCount();
                table.incrementBatchFailureCount();
                lastBatchFailure = true;
            }
            insertStmt.clearBatch();
            this.targetConn.commit();
        } catch (SQLException sqle) {
            this.targetConn.rollback();//
            throw (sqle.getNextException() != null ? sqle.getNextException()
                    : sqle);
        } catch (Exception ex) {
            this.targetConn.rollback();
            throw ex;
        } finally {
            for (int i = 0; i < arrFis.length; i++) {
                if (arrFis[i] != null) {
                    arrFis[i].close();
                    new File(Utility.getTempPath() + "mtk_blobdata" + "_"
                            + fileNamePrefix + i).delete();
                }

                if (arrBis[i] != null) {
                    arrBis[i].close();
                    arrBis[i] = null;
                }

            }

            for (int i = 0; i < arrBr.length; i++) {
                if (arrBr[i] != null) {
                    arrBr[i].close();
                    new File(Utility.getTempPath() + "mtk_clobdata" + "_"
                            + fileNamePrefix + i).delete();
                }

            }

            this.targetConn.getConnection().setAutoCommit(true);

            if ((((this.srcConn instanceof EnterpriseDBConnection)) || ((this.srcConn instanceof SRDBConnection)))
                    && (_edbConn != null)) {
                _edbConn.setAutoCommit(defaultAutoCommitMode);
            }

            if (insertStmt != null) {
                insertStmt.close();
            }

            if (destStmt != null) {
                destStmt.close();
            }

            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
//                    ex.printStackTrace();
                }
            }

            if (sourceStmt != null) {
                try {
                    sourceStmt.close();
                } catch (Exception ex) {
//                    ex.printStackTrace();
                }
            }
        }
        return committedRowCount;
    }

    public long setTableDataInSafeMode(Table table,
                                       MTKConstants.DATABASE_TYPE sourceDBType,
                                       MTKConstants.DATABASE_TYPE targetDBType) throws Exception {
        Statement insertStmt = null;
        Statement sourceStmt = null;
        Statement destStmt = null;
        ResultSet rs = null;
        StringBuffer insertColStr = new StringBuffer();
        StringBuffer insertStr = new StringBuffer();
        Connection _edbConn = null;
        boolean defaultAutoCommitMode = false;
        try {
            insertColStr.append("INSERT INTO ")
                    .append(table.getTargetSchemaQualifiedName())
                    .append(" VALUES (");

            insertStmt = this.targetConn.getConnection().createStatement();
            destStmt = this.targetConn.getConnection().createStatement();
            destStmt.execute("TRUNCATE TABLE "
                    + table.getTargetSchemaQualifiedName());

            sourceStmt = this.srcConn.getConnection().createStatement();

            if (((this.srcConn instanceof EnterpriseDBConnection))
                    || ((this.srcConn instanceof SRDBConnection))) {
                _edbConn = this.srcConn.getConnection();
                defaultAutoCommitMode = _edbConn.getAutoCommit();
                _edbConn.setAutoCommit(false);

                sourceStmt.setFetchSize(100);
            }

            rs = sourceStmt.executeQuery(table.getSelectSQL());

            int rowCount = 0;
            while (rs.next()) {
                insertStr.setLength(0);
                insertStr.append(insertColStr);

                for (int i = 0; i < table.getColumns().size(); i++) {
                    if (i > 0) {
                        insertStr.append(", ");
                    }

                    if (rs.getObject(i + 1) == null) {
                        insertStr.append("null");
                    } else {
                        switch (Column.DataType.values()[table.getColumns()
                                .get(i).getDataType().ordinal()]) {
                            case VARCHAR:
                            case NVARCHAR:
                            case TEXT:
                            case NAME:
                            case BOOLEAN:
                                insertStr.append("'").append(rs.getString(i + 1))
                                        .append("'");
                                break;
                            case INTEGER:
                            case FLOAT:
                            case NUMERIC:
                            case MONEY:
                                insertStr.append(rs.getBigDecimal(i + 1));
                                break;
                            case DATE:
                                insertStr.append("'").append(rs.getDate(i + 1))
                                        .append("'");
                                break;
                            case TIME:
                                insertStr.append("'").append(rs.getTime(i + 1))
                                        .append("'");
                                break;
                            case TIMESTAMP:
                                insertStr.append("'")
                                        .append(rs.getTimestamp(i + 1)).append("'");
                                break;
                            case BYTEA:
                                insertStr.append("null");
                                break;
                            case BIT:
                            case BITSTRING:
                                insertStr.append("'").append(rs.getString(i + 1))
                                        .append("'");
                                break;
                            // case 13:
                            // case 14:
                            // case 15:
                            // case 16:
                            // case 17:
                            // case 19:
                            // case 20:
                            // case 21:
                            default:
                                throw new Exception("Invalid DataType");
                        }
                    }
                }
                insertStr.append(")");
                // System.out.println("safe-m: "+insertStr.toString());
                insertStmt.executeUpdate(insertStr.toString());

                rowCount++;
            }

            int i = rowCount;
            return new Long(i);
        } finally {
            if ((((this.srcConn instanceof EnterpriseDBConnection)) || ((this.srcConn instanceof SRDBConnection)))
                    && (_edbConn != null)) {
                _edbConn.setAutoCommit(defaultAutoCommitMode);
            }

            if (insertStmt != null) {
                insertStmt.close();
            }

            if (destStmt != null) {
                destStmt.close();
            }

            if (rs != null) {
                rs.close();
            }

            if (sourceStmt != null)
                sourceStmt.close();
        }

    }

    private static SRInterval getPGIntervalObject(String intervalValue) {
        String[] arrInterval = RedwoodBridge
                .convertToPGIntervalFormat(intervalValue);
        return new SRInterval(Integer.parseInt(arrInterval[0]),
                Integer.parseInt(arrInterval[1]),
                Integer.parseInt(arrInterval[2]),
                Integer.parseInt(arrInterval[3]),
                Integer.parseInt(arrInterval[4]),
                Double.parseDouble(arrInterval[5]));
    }

    protected class RedwoodLobHandler {
        protected RedwoodLobHandler() {
        }

        public void processClob(ResultSet rs, int i, String fileNamePrefix,
                                int brIndex, BufferedReader[] arrBr,
                                PreparedStatement insertStmt) throws IOException, SQLException {
            CLOB clob = ((OracleResultSet) rs).getCLOB(i + 1);
            if (clob != null) {
                long clobLength = clob.length();
                // SRDBBridge.access$014(SRDBBridge.this, clobLength);

                Reader creader = clob.getCharacterStream();

                int size = clob.getBufferSize();
                char[] buffer = new char[size];

                File clobFile = new File(Utility.getTempPath() + "mtk_clobdata"
                        + "_" + fileNamePrefix + brIndex);

                if (SRDBBridge.fileCharEncoding == null) {
                    String prm_NLS_CHARACTERSET_Value = MigrationToolkit
                            .getInstance().getSrcConn()
                            .getDBServerParamValue("NLS_CHARACTERSET");
                    if (prm_NLS_CHARACTERSET_Value != null) {
                        if (prm_NLS_CHARACTERSET_Value
                                .equalsIgnoreCase("US7ASCII"))
                            SRDBBridge.fileCharEncoding = "US-ASCII";
                        else if (prm_NLS_CHARACTERSET_Value
                                .equalsIgnoreCase("AL16UTF16"))
                            SRDBBridge.fileCharEncoding = "UTF-16";
                        else
                            SRDBBridge.fileCharEncoding = "UTF-8";
                    } else {
                        SRDBBridge.fileCharEncoding = "UTF-8";
                    }
                }

                BufferedWriter cbw = new BufferedWriter(new OutputStreamWriter(
                        new FileOutputStream(clobFile),
                        SRDBBridge.fileCharEncoding));
                int length = -1;
                while ((length = creader.read(buffer)) != -1) {
                    if (MigrationToolkit.replaceNullChar()) {
                        for (int index = 0; index < length; index++) {
                            if (buffer[index] == 0) {
                                buffer[index] = MigrationToolkit
                                        .nullReplacementChar();
                            }
                        }
                    }
                    cbw.write(buffer, 0, length);
                }
                creader.close();
                cbw.close();
                if (clobLength > 0L) {
                    arrBr[brIndex] = new BufferedReader(new InputStreamReader(
                            new FileInputStream(clobFile),
                            SRDBBridge.fileCharEncoding));
                    insertStmt.setCharacterStream(i + 1, arrBr[brIndex],
                            (int) clobFile.length());
                } else {
                    insertStmt.setNull(i + 1, 2005);
                }
            } else {
                insertStmt.setNull(i + 1, 2005);
            }
        }

        public int processBlob(ResultSet rs, int i, String fileNamePrefix,
                               int fisIndex, FileInputStream[] arrFis,
                               PreparedStatement insertStmt) throws IOException, SQLException {
            BLOB blob = ((OracleResultSet) rs).getBLOB(i + 1);
            if (blob != null) {
                long blobLength = blob.length();
                // SRDBBridge.access$114(SRDBBridge.this, blobLength);

                InputStream bis = blob.getBinaryStream();

                int size = blob.getBufferSize();
                byte[] buffer = new byte[size];

                File blobFile = new File(Utility.getTempPath() + "mtk_blobdata"
                        + "_" + fileNamePrefix + fisIndex);
                System.out.println(blobFile.getAbsolutePath());
                FileOutputStream bos = new FileOutputStream(blobFile);
                int length = -1;
                while ((length = bis.read(buffer)) != -1) {
                    bos.write(buffer, 0, length);
                }
                bis.close();
                bos.close();
                if (blobLength > 0L) {
                    arrFis[fisIndex] = new FileInputStream(blobFile);
                    insertStmt.setBinaryStream(i + 1, arrFis[(fisIndex++)],
                            (int) blobFile.length());
                } else {
                    insertStmt.setNull(i + 1, -2);
                }
            } else {
                insertStmt.setNull(i + 1, -2);
            }

            return fisIndex;
        }
    }

}

/*

 */